﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Drawing;
using System.Data;
using System.Text;
using System.Windows.Forms;

namespace SP.OpenSource.CDC.Monitor
{
    public partial class CDCManagement : UserControl
    {
        String m_sConnectedConfigID = String.Empty;
        String m_sConnectionString = String.Empty;
        String m_sConnectedDBName = String.Empty;
        String m_sConnectedServerName = String.Empty;
        DataTable m_dtCDCEnabledTables = new DataTable("CDCEnabledTables");
        DataTable m_dtCDCTableColumns = new DataTable("CDCTableColumns");

        String m_sErrorMessage = String.Empty;
        String m_sFormTitle = "CDC Management";
        String m_sGenericErrorMessage = "Exception raised while processing request." + Environment.NewLine + "Error Message: ";

        BLL.CDCManagement m_oCDCManagement = new BLL.CDCManagement();

        public CDCManagement()
        {
            InitializeComponent();
        }

        public CDCManagement(String sConnectionString, String sConnectedDBName, String sConnectedServerName, String sConnectedConfigID)
        {
            InitializeComponent();
            m_sConnectedConfigID = sConnectedConfigID;
            m_sConnectionString = sConnectionString;
            m_sConnectedDBName = sConnectedDBName;
            m_sConnectedServerName = sConnectedServerName;
        }

        private void chkIncludeAllColumns_CheckedChanged(object sender, EventArgs e)
        {
            if (txtNewTableName.Text.Length <= 0 || txtNewSchemaName.Text.Length <= 0)
            {
                MessageBox.Show("Tablename and Schema name cannot be blank.", m_sFormTitle + " - Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return;
            }
            if (true == chkIncludeAllColumns.Checked)
            {
                grdCaptureColumns.Visible = false;
                lblNoColumnsFound.Visible = false;
            }
            else
            {
                BindGridCDCTableColumns();
                AddSelectAllCheckBox();
                grdCaptureColumns.ColumnHeadersHeight = 25;        
            }
        }

        private void AddSelectAllCheckBox()
        {
            try
            {
                Rectangle rect = grdCaptureColumns.GetCellDisplayRectangle(0, -1, true);
                //rect.X = rect.Location.X + (rect.Width / 4);
                rect.X = 6;
                rect.Y = 3;

                CheckBox checkboxHeader = new CheckBox();
                checkboxHeader.Name = "checkboxHeader";
                checkboxHeader.Size = new Size(15, 15);
                checkboxHeader.FlatStyle = FlatStyle.Standard;
                checkboxHeader.Location = rect.Location;

                checkboxHeader.CheckedChanged += new EventHandler(checkboxHeader_CheckedChanged);

                grdCaptureColumns.Controls.Add(checkboxHeader);
                grdCaptureColumns.ColumnHeadersHeight = 25;
            }
            catch (Exception ex)
            {
                MessageBox.Show(m_sGenericErrorMessage + ex.Message, m_sFormTitle + " - Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }
        private void checkboxHeader_CheckedChanged(object sender, EventArgs e)
        {
            try
            {
                CheckBox cbx = (CheckBox)sender;
                if (null != cbx)
                {
                    foreach (DataGridViewRow row in grdCaptureColumns.Rows)
                    {
                        if (true == cbx.Checked)
                            row.Cells["SelectColumn"].Value = "1";
                        else
                            row.Cells["SelectColumn"].Value = "0";
                    }
                }

                grdCaptureColumns.EndEdit();
            }
            catch (Exception ex)
            {
                MessageBox.Show(m_sGenericErrorMessage + ex.Message, m_sFormTitle + " - Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }
        
        public bool LoadCDCManagementInfo()
        {
            try
            {   
                m_sErrorMessage = String.Empty;
                if ("1" == m_oCDCManagement.IsUserSysAdmin(m_sConnectionString, m_sConnectedDBName, ref m_sErrorMessage) ||
                     "1" == m_oCDCManagement.IsUserDBOwner(m_sConnectionString, m_sConnectedDBName, ref m_sErrorMessage))
                {
                    grpNotSysAdmin.Visible = false;
                    m_sErrorMessage = String.Empty;

                    if (true == m_oCDCManagement.IsCDCEnabledDB(m_sConnectionString, m_sConnectedDBName, ref m_sErrorMessage))
                    {
                        lblDBCDCStatus.Text = "Enabled";
                        lblDBCDCStatus.ForeColor = Color.Green;
                        CDCGUISettings(true);
                    }
                    else
                    {
                        lblDBCDCStatus.Text = "Disabled";
                        lblDBCDCStatus.ForeColor = Color.Red;
                        CDCGUISettings(false);
                    }

                    m_dtCDCEnabledTables = m_oCDCManagement.GetCDCEnabledTables(m_sConnectionString, ref m_sErrorMessage);

                    if (null != m_dtCDCEnabledTables && m_dtCDCEnabledTables.Rows.Count > 0)
                    {
                        CDCTablesGUISettings(true);
                        CDCTablesGridSettings();
                        EnableGUINewCDCTable(false);
                        BindGridCDCTables();
                    }
                    else
                    {
                        CDCTablesGUISettings(false);
                        CDCTablesGridSettings();
                    }

                    grpCDCTable.Visible = true;
                }
                else
                {
                    grpNotSysAdmin.Visible = true;
                    grpCDCDB.Visible = false;
                    grpCDCTable.Visible = false;
                    grpCDCTableDetails.Visible = false;
                }
                return true;
            }
            catch (Exception ex)
            {
                MessageBox.Show(m_sGenericErrorMessage + ex.Message, m_sFormTitle + " - Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return false;
            }
        }

        void CDCGUISettings(bool bStatus)
        {
            if (bStatus == true)
            {
                grpCDCTable.Visible = true;
                
                btnCDCDisableDB.Visible = true;
                btnCDCEnableDB.Visible = false;
                btnEnableCDConTable.Visible = true;
            }
            else
            {
                grpCDCTable.Visible = false;
                
                btnCDCDisableDB.Visible = false;
                btnCDCEnableDB.Visible = true;
                btnCDCEnableDB.Enabled = true;
                btnEnableCDConTable.Visible = false;
            }

            grpCDCTableDetails.Visible = false;

            grpSelectedCDCTableDtls.Visible = false;
        }

        void CDCTablesGUISettings(bool bStatus)
        {
            if (bStatus == true)
            {
                grpCDCTable.Visible = true;                
            }
            else
            {
                grpCDCTable.Visible = false;
            }

            grpCDCTableDetails.Visible = false;                
            if (lblDBCDCStatus.Text == "Enabled")
            {
                btnCDCEnableDB.Visible = false;
                btnCDCDisableDB.Visible = true;
            }
            else
            {
                btnCDCEnableDB.Visible = true;
                btnCDCDisableDB.Visible = false;
            }
            
        }

        void CDCTablesGridSettings()
        {
            if (null != m_dtCDCEnabledTables && m_dtCDCEnabledTables.Rows.Count > 0)
            {
                lblStatusCDCEnabledTables.Visible = false;
                grdCDCEnabledTables.Visible = true;

                btnGenDisableScripts.Visible = true;
                btnGenEnableScripts.Visible = true;
                
            }
            else
            {
                lblStatusCDCEnabledTables.Visible = true;
                grdCDCEnabledTables.Visible = false;
                btnGenDisableScripts.Visible = false;
                btnGenEnableScripts.Visible = false;
                
            }
            btnCDCEnableTable.Visible = true;
            btnCDCEnableTable.Enabled = true;

            grpSelectedCDCTableDtls.Visible = false;
            btnDisableCDCTable.Visible = false;
        }
        void EnableGUINewCDCTable(bool bStatus)
        {
            if (bStatus == true)
            {
                grpCDCTableDetails.Visible = true;
                
            }
            else
            {
                grpCDCTableDetails.Visible = false;
            }
        }

        void BindGridCDCTables()
        {
            try
            {
                DataTable dtCDCEnabledTables = m_dtCDCEnabledTables.DefaultView.ToTable(true,new String[] {"table_fullname", "source_table", "source_schema"});
                grdCDCEnabledTables.DataSource = dtCDCEnabledTables;

                grdCDCEnabledTables.ClearSelection();
            }
            catch (Exception ex)
            {
                MessageBox.Show(m_sGenericErrorMessage + ex.Message, m_sFormTitle + " - Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }

        bool BindGridCDCTableColumns()
        {
            try
            {
                m_sErrorMessage = String.Empty;

                m_dtCDCTableColumns = m_oCDCManagement.GetCDCTableColumns(m_sConnectionString, txtNewSchemaName.Text, txtNewTableName.Text, ref m_sErrorMessage);

                if (null != m_dtCDCTableColumns && m_dtCDCTableColumns.Rows.Count > 0)
                {

                    grdCaptureColumns.Visible = true;
                    lblNoColumnsFound.Visible = false;
                    grdCaptureColumns.DataSource = m_dtCDCTableColumns;
                    return true;

                    
                }
                else
                {
                    grdCaptureColumns.Visible = false;
                    lblNoColumnsFound.Visible = true;
                    MessageBox.Show("There was an error while fetching column information.", m_sFormTitle, MessageBoxButtons.OK, MessageBoxIcon.Error);
                    return false;
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(m_sGenericErrorMessage + ex.Message, m_sFormTitle + " - Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return false;
            }
        }

        private void btnEnableCDConTable_Click(object sender, EventArgs e)
        {
            try
            {
                grpCDCDB.Visible = false;
                grpCDCTable.Visible = false;
                grpCDCTableDetails.Visible = true;
                chkIncludeAllColumns.Visible = false;

                CleanEnteredData();
                cmbAllowPartitionSwitch.Text = "Yes";
                cmbSupportsNetChanges.Text = "All Changes";
                
            }
            catch (Exception ex)
            {
                MessageBox.Show(m_sGenericErrorMessage + ex.Message, m_sFormTitle + " - Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }


        private void grdCDCEnabledTables_RowHeaderMouseClick(object sender, DataGridViewCellMouseEventArgs e)
        {
            try
            {
                if (e.RowIndex >= 0)
                {
                    String sFullTableName = grdCDCEnabledTables.Rows[e.RowIndex].Cells["TableFullName"].Value.ToString();
                    String sTableName = grdCDCEnabledTables.Rows[e.RowIndex].Cells["TableName"].Value.ToString();
                    String sSchemaName = grdCDCEnabledTables.Rows[e.RowIndex].Cells["SchemaName"].Value.ToString();
                    
                    DataTable dtCapturedColumns = m_dtCDCEnabledTables.DefaultView.ToTable(true, new String[] { "capture_instance" });
                    lstSelectedCaptureInstance.DataSource = dtCapturedColumns;
                    lstSelectedCaptureInstance.DisplayMember = "capture_instance";
                    lstSelectedCaptureInstance.ValueMember = "capture_instance";

                    DataRow[] drSelected = m_dtCDCEnabledTables.Select("table_fullname='" + sFullTableName + "'");

                    txtSelectedColumns.Text = drSelected[0]["captured_column_list"].ToString();
                    txtSelectedFileGroup.Text = drSelected[0]["filegroup_name"].ToString();

                    txtSelectedIndexName.Text = drSelected[0]["index_name"].ToString();
                    txtSelectedRoleName.Text  = drSelected[0]["role_name"].ToString();
                    txtSelectedIndexColumns.Text = drSelected[0]["index_column_list"].ToString();

                    if (drSelected[0]["support_net_changes"].ToString() == "0")
                    {
                        txtSelectedNetChanges.Text = "All Changes";
                    }
                    else
                        txtSelectedNetChanges.Text = "All Changes (Including net changes)";
                    
                }
                else
                {
                    grpSelectedCDCTableDtls.Visible = false;
                    btnDisableCDCTable.Visible = false;
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(m_sGenericErrorMessage + ex.Message, m_sFormTitle + " - Error", MessageBoxButtons.OK, MessageBoxIcon.Error);

            }

        }

        private void grdCDCEnabledTables_DataBindingComplete(object sender, DataGridViewBindingCompleteEventArgs e)
        {
            grdCDCEnabledTables.ClearSelection();
        }

        
        private void grdCDCEnabledTables_CellClick(object sender, DataGridViewCellEventArgs e)
        {
            try
            {
                if (e.RowIndex >= 0)
                {
                    lblSelectedTable.Text = grdCDCEnabledTables.Rows[e.RowIndex].Cells["TableFullName"].Value.ToString();
                    String sFullTableName = grdCDCEnabledTables.Rows[e.RowIndex].Cells["TableFullName"].Value.ToString();
                    String sTableName = grdCDCEnabledTables.Rows[e.RowIndex].Cells["TableName"].Value.ToString();
                    String sSchemaName = grdCDCEnabledTables.Rows[e.RowIndex].Cells["SchemaName"].Value.ToString();

                    String sFilter = "source_schema='" + sSchemaName  + "' AND ";
                    sFilter += " source_table='" + sTableName + "'";
        
                    DataRow[] drSelected = m_dtCDCEnabledTables.Select(sFilter);
        

                    DataTable dtCapturedColumns = m_dtCDCEnabledTables.Clone();
                    foreach (DataRow rSelected in drSelected)
                    {
                        dtCapturedColumns.Rows.Add(rSelected.ItemArray);
                    }
                    dtCapturedColumns.AcceptChanges();

                    lstSelectedCaptureInstance.DataSource = dtCapturedColumns;
                    lstSelectedCaptureInstance.DisplayMember = "capture_instance";
                    lstSelectedCaptureInstance.ValueMember = "capture_instance";

                    txtSelectedColumns.Text = drSelected[0]["captured_column_list"].ToString();
                    txtSelectedFileGroup.Text = drSelected[0]["filegroup_name"].ToString();

                    txtSelectedIndexName.Text = drSelected[0]["index_name"].ToString();
                    txtSelectedRoleName.Text = drSelected[0]["role_name"].ToString();
                    txtSelectedIndexColumns.Text = drSelected[0]["index_column_list"].ToString();

                    if (drSelected[0]["supports_net_changes"].ToString() == "0")
                    {
                        txtSelectedNetChanges.Text = "All Changes";
                    }
                    else
                        txtSelectedNetChanges.Text = "All Changes (Including net changes)";

                    grpSelectedCDCTableDtls.Visible = true;
                    btnDisableCDCTable.Visible = true;
                }
                else
                {
                    lblSelectedTable.Text = String.Empty;
                    grpSelectedCDCTableDtls.Visible = false;
                    btnDisableCDCTable.Visible = false;
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("There was an error fetching CDC enabled table details. Message: " + ex.Message, m_sFormTitle + " - Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }

        private void txtNewTableName_TextChanged(object sender, EventArgs e)
        {
            try
            {
                if (txtNewTableName.Text.Length > 0 &&
                    txtNewSchemaName.Text.Length > 0)
                {
                    txtNewCaptureInstance.Text = txtNewSchemaName.Text + "." + txtNewTableName.Text;
                    chkIncludeAllColumns.Visible = true;
                    chkIncludeAllColumns.Checked = true;
                }
                else
                    chkIncludeAllColumns.Visible = false;

            }
            catch (Exception ex)
            {
                MessageBox.Show(m_sGenericErrorMessage + ex.Message, m_sFormTitle + " - Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }

        private void btnCDCEnableTable_Click(object sender, EventArgs e)
        {
            try
            {
                Cursor.Current = Cursors.WaitCursor;
                if (txtNewSchemaName.Text.Length <= 0)
                {
                    Cursor.Current = Cursors.Default;
                    MessageBox.Show("Schema name cannot be blank","Enable CDC on table - Validation error",MessageBoxButtons.OK,MessageBoxIcon.Error);
                    return;
                }
                if (txtNewTableName.Text.Length <= 0)
                {
                    Cursor.Current = Cursors.Default;
                    MessageBox.Show("Table name cannot be blank", "Enable CDC on table - Validation error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                    return;
                }
                if (txtNewCaptureInstance.Text.Length <= 0)
                {
                    Cursor.Current = Cursors.Default;
                    MessageBox.Show("Capture instance cannot be blank", "Enable CDC on table - Validation error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                    return;
                }
                if (txtNewCaptureInstance.Text.Length <= 0)
                {
                    Cursor.Current = Cursors.Default;
                    MessageBox.Show("Capture instance cannot be blank", "Enable CDC on table - Validation error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                    return;
                }
                String sCapturedColumnList = String.Empty;
                
                if (chkIncludeAllColumns.Checked == false)
                {
                    if (null == m_dtCDCTableColumns || m_dtCDCTableColumns.Rows.Count <= 0)
                    {
                        Cursor.Current = Cursors.Default;
                        MessageBox.Show("Capture columns cannot be blank", "Enable CDC on table - Validation error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                        return;
                    }

                    
                    DataRow[] drSelected = m_dtCDCTableColumns.Select("COLUMN_SELECTION='1'");
                    if (drSelected.Length <= 0)
                    {
                        Cursor.Current = Cursors.Default;
                        MessageBox.Show("Capture columns cannot be blank", "Enable CDC on table - Validation error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                        return;
                    }
                    foreach (DataRow drSel in drSelected)
                    {
                        sCapturedColumnList += drSel["COLUMN_NAME"].ToString() + ",";
                    }

                    sCapturedColumnList = BLL.Utils.RemoveLastChar(sCapturedColumnList, ",");
                }

                String sDDLEnableCDC = "EXEC sys.sp_cdc_enable_table ";
                sDDLEnableCDC += "@source_schema = N'" + txtNewSchemaName.Text + "', ";
                sDDLEnableCDC += "@source_name = N'" + txtNewTableName.Text + "', ";
                sDDLEnableCDC += "@role_name = N'" + txtNewRoleName.Text + "', ";
                sDDLEnableCDC += "@capture_instance = N'" + txtNewCaptureInstance.Text + "', ";

                if (cmbSupportsNetChanges.Text == "All Changes")
                    sDDLEnableCDC += "@supports_net_changes = 0,";
                else
                    sDDLEnableCDC += "@supports_net_changes = 1,";

                if(txtNewIndexName.Text.Length > 0)
                    sDDLEnableCDC += "@index_name = N'" + txtNewIndexName.Text + "', ";
                else
                    sDDLEnableCDC += "@index_name = null ,";

                if (false == chkIncludeAllColumns.Checked)
                {

                    sDDLEnableCDC += "@captured_column_list  = N'" + sCapturedColumnList + "', ";
                }
                else
                    sDDLEnableCDC += "@captured_column_list  = null ,";

                if (txtFilegroupName.Text.Length > 0)
                    sDDLEnableCDC += "@filegroup_name = N'" + txtFilegroupName.Text + "', ";
                else
                    sDDLEnableCDC += "@filegroup_name = null ,";

                if (cmbAllowPartitionSwitch.Text == "Yes")
                    sDDLEnableCDC += "@allow_partition_switch = 1";
                else
                    sDDLEnableCDC += "@allow_partition_switch = 0";

                m_sErrorMessage = String.Empty;
                if (false == m_oCDCManagement.CDCEnabledTable(m_sConnectionString, sDDLEnableCDC, ref m_sErrorMessage))
                {
                    Cursor.Current = Cursors.Default;
                    String sErrorMessage = "There was an error while enabling CDC on " + txtNewTableName.Text + "." ;
                    sErrorMessage += Environment.NewLine;
                    sErrorMessage += Environment.NewLine;
                    sErrorMessage += "Error message : " + m_sErrorMessage;
                    MessageBox.Show(sErrorMessage, "Enable CDC on table - Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                    return;
                }

                LoadCDCManagementInfo();
                
                grpCDCDB.Visible = true;
                Cursor.Current = Cursors.Default;
                MessageBox.Show("CDC was enabled on table - " + txtNewSchemaName.Text + "."+ txtNewTableName.Text + ".", "Enable CDC on table - success", MessageBoxButtons.OK, MessageBoxIcon.Information);

            }
            catch (Exception ex)
            {
                btnEnableCDConTable.Visible = true;
                btnEnableCDConTable.Enabled = false;
                Cursor.Current = Cursors.Default;
                MessageBox.Show("There was an issue while enabling CDC on table. Make sure user has DB_OWNER or SYSADMIN rights.", "Enable CDC on table - Validation error", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }

        private void btnCancelCDCEnable_Click(object sender, EventArgs e)
        {   
            CleanEnteredData();
            grdCDCEnabledTables.ClearSelection();
            grpCDCDB.Visible = true;
            grpCDCTableDetails.Visible = false;
            
            grpCDCTable.Visible = true;

            btnCDCDisableDB.Enabled = true;
            btnCDCEnableTable.Visible = true;
            btnCDCEnableTable.Enabled = true;

            btnEnableCDConTable.Visible = true;
            btnEnableCDConTable.Enabled = true;
            chkIncludeAllColumns.Checked = true;
            grdCaptureColumns.Visible = false;
            
        }

        
        void CleanEnteredData()
        {
            txtNewCaptureInstance.Text = String.Empty;
            txtNewIndexName.Text = String.Empty;
            txtNewRoleName.Text = "CDC";
            txtNewSchemaName.Text = String.Empty;
            txtNewTableName.Text = String.Empty;
            txtFilegroupName.Text = String.Empty;

            cmbSupportsNetChanges.Text = "All Changes (Including net changes)";
            cmbSupportsNetChanges.Text = "Yes";
            chkIncludeAllColumns.Checked = true;
            lblNoColumnsFound.Visible = false;
            grdCaptureColumns.Visible = false;

            CDCTablesGridSettings();
        }

        private void btnCDCEnableDB_Click(object sender, EventArgs e)
        {
            try
            {
                Cursor.Current = Cursors.WaitCursor;
                m_sErrorMessage = String.Empty;
                if (false == m_oCDCManagement.CDCEnabledDB(m_sConnectionString, ref m_sErrorMessage))
                {
                    Cursor.Current = Cursors.Default;
                    CDCGUISettings(false);
                    String sErrorMessage = "There was an error while enabling CDC. Make sure the user has SYSADMIN rights.";
                    sErrorMessage += Environment.NewLine;
                    sErrorMessage += Environment.NewLine;
                    sErrorMessage += "If the database was restored or attached, execute the following command on the database, before enabling CDC.";
                    sErrorMessage += Environment.NewLine;
                    sErrorMessage += "EXEC sp_changedbowner 'sa'";
                    sErrorMessage += Environment.NewLine;
                    sErrorMessage += Environment.NewLine;
                    sErrorMessage += Environment.NewLine;
                    sErrorMessage += "Exception message: " + m_sErrorMessage;

                    MessageBox.Show(sErrorMessage, m_sFormTitle, MessageBoxButtons.OK, MessageBoxIcon.Error);
                    m_sErrorMessage = String.Empty;
                    return;
                }
                else
                {
                    Cursor.Current = Cursors.Default;
                    String sMessage = String.Empty;

                    bool bLoggedOff = false;
                    TabControl tc = (TabControl)((TabPage)(this.Parent)).Parent;

                    Form tForm = (Form)tc.Parent;

                    if (null != tForm)
                    {
                        MonitorCDCMain oMonitorCDCMain = (MonitorCDCMain)tForm;

                        if (null != oMonitorCDCMain)
                        {
                            bLoggedOff = true;
                            sMessage = "CDC was enabled on " + m_sConnectedDBName + " database. ";
                            sMessage += Environment.NewLine;
                            sMessage += Environment.NewLine;
                            sMessage += "You will be logged off for the changes to be affected.";

                            MessageBox.Show(sMessage, m_sFormTitle, MessageBoxButtons.OK, MessageBoxIcon.Information);

                            oMonitorCDCMain.m_bLogout = true;
                            oMonitorCDCMain.Close();
                            return;
                        }

                    }

                    if (bLoggedOff == false)
                    {
                        CDCGUISettings(true);

                        lblDBCDCStatus.Text = "Enabled";
                        lblDBCDCStatus.ForeColor = Color.Green;

                        sMessage = "CDC was enabled on " + m_sConnectedDBName + " database. ";
                        sMessage += Environment.NewLine;
                        sMessage += Environment.NewLine;

                        sMessage += "Please logoff and connnect to the " + m_sConnectedDBName + " database.";

                        MessageBox.Show(sMessage, m_sFormTitle, MessageBoxButtons.OK, MessageBoxIcon.Information);
                    }
                }

                
            }
            catch (Exception ex)
            {
                Cursor.Current = Cursors.Default;

                String sErrorMessage = "There was an error while enabling CDC. Make sure the user has SYSADMIN rights.";
                sErrorMessage += Environment.NewLine;
                sErrorMessage += Environment.NewLine;
                sErrorMessage += "If the database was restored or attached, execute the following command on the database, before enabling CDC.";
                sErrorMessage += Environment.NewLine;
                sErrorMessage += "EXEC sp_changedbowner 'sa'";
                sErrorMessage += Environment.NewLine;
                sErrorMessage += Environment.NewLine;
                sErrorMessage += Environment.NewLine;
                sErrorMessage += "Exception message: " + ex.Message;

                MessageBox.Show(sErrorMessage, m_sFormTitle, MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }

        private void btnCDCDisableDB_Click(object sender, EventArgs e)
        {
            try
            {
                if (DialogResult.Yes == MessageBox.Show("Are you sure you want to disable CDC on database?","CDC Management",MessageBoxButtons.YesNo,MessageBoxIcon.Question))
                {
                    Cursor.Current = Cursors.WaitCursor;
                    //disable tables first
                    String sDdlDisableCDCTable = String.Empty;

                    foreach (DataRow drCDCTables in m_dtCDCEnabledTables.Rows)
                    {
                        sDdlDisableCDCTable = "EXEC sys.sp_cdc_disable_table ";
                        sDdlDisableCDCTable += Environment.NewLine;
                        sDdlDisableCDCTable += " @source_schema = '" + drCDCTables["source_schema"].ToString() + "',";
                        sDdlDisableCDCTable += Environment.NewLine;
                        sDdlDisableCDCTable += " @source_name = '" + drCDCTables["source_table"].ToString() + "',";
                        sDdlDisableCDCTable += Environment.NewLine;
                        sDdlDisableCDCTable += " @capture_instance = '" + drCDCTables["capture_instance"].ToString() + "'";

                        m_sErrorMessage = String.Empty;

                        if(false == m_oCDCManagement.CDCDisableCDCTable(m_sConnectionString, ref sDdlDisableCDCTable, ref m_sErrorMessage))
                        {
                            Cursor.Current = Cursors.Default;
                            CDCGUISettings(true);
                            MessageBox.Show("There was an error while disabling CDC." +Environment.NewLine + " Error Message: " + m_sErrorMessage, m_sFormTitle, MessageBoxButtons.OK, MessageBoxIcon.Error);
                            return;
                        }
                    }

                    m_sErrorMessage = String.Empty;
                    if (false == m_oCDCManagement.CDCDisableDB(m_sConnectionString, ref m_sErrorMessage))
                    {
                        Cursor.Current = Cursors.Default;
                        CDCGUISettings(true);
                        MessageBox.Show("There was an error while disabling CDC." + Environment.NewLine + " Error Message: " + m_sErrorMessage, m_sFormTitle, MessageBoxButtons.OK, MessageBoxIcon.Error);
                    }

                    CDCGUISettings(false);
                    lblDBCDCStatus.Text = "Disabled";
                    lblDBCDCStatus.ForeColor = Color.Red;
                    Cursor.Current = Cursors.Default;
                    
                    String sMessage = "CDC was disabled on " + m_sConnectedDBName + " database. ";
                    sMessage += Environment.NewLine;
                    sMessage += Environment.NewLine;
                    sMessage += "Please logoff.";

                    bool bLoggedOff = false;
                    TabControl tc = (TabControl)((TabPage)(this.Parent)).Parent;

                    Form tForm = (Form)tc.Parent;

                    if (null != tForm)
                    {
                        MonitorCDCMain oMonitorCDCMain = (MonitorCDCMain)tForm;

                        if (null != oMonitorCDCMain)
                        {
                            bLoggedOff = true;
                            sMessage = "CDC was disabled on " + m_sConnectedDBName + " database. ";
                            sMessage += Environment.NewLine;
                            sMessage += Environment.NewLine;
                            sMessage += "You will be logged off for the changes to be affected.";

                            MessageBox.Show(sMessage, m_sFormTitle, MessageBoxButtons.OK, MessageBoxIcon.Information);

                            oMonitorCDCMain.m_bLogout = true;
                            oMonitorCDCMain.Close();
                        }
                     
                    }

                    if (bLoggedOff == false)
                    {
                        MessageBox.Show(sMessage, m_sFormTitle, MessageBoxButtons.OK, MessageBoxIcon.Information);
                    }
                }
            }
            catch (Exception ex)
            {
                Cursor.Current = Cursors.Default;

                MessageBox.Show("There was an error while disabling CDC. Make sure the user has SYSADMIN rights.", m_sFormTitle, MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }

        private void btnDisableCDC_Click(object sender, EventArgs e)
        {
            try
            {
                if (DialogResult.Yes == MessageBox.Show("Are you sure you want to disable CDC on table?", "CDC Management", MessageBoxButtons.YesNo, MessageBoxIcon.Question))
                {
                    DataTable dtCapturedInsatnce = m_dtCDCEnabledTables.DefaultView.ToTable(true, new String[] { "source_schema", "source_table", "table_fullname", "capture_instance" });
                    if (lblSelectedTable.Text.Length <= 0)
                    {
                        MessageBox.Show("CDC enabled table must be selected");
                        return;
                    }

                    DataRow drNew = dtCapturedInsatnce.NewRow();
                    drNew["table_fullname"] = lblSelectedTable.Text;
                    drNew["capture_instance"] = " All";
                    dtCapturedInsatnce.Rows.InsertAt(drNew, 0);
                    dtCapturedInsatnce.AcceptChanges();
                    dtCapturedInsatnce.DefaultView.Sort = "capture_instance";
                    DataRow[] drSelected = dtCapturedInsatnce.Select("table_fullname = '" + lblSelectedTable.Text + "'");


                    DisableCDCTable oDisableCDCTable = new DisableCDCTable(ref drSelected);
                    oDisableCDCTable.ShowDialog();

                    if (oDisableCDCTable.m_sSelectedCaptureInstance.Length <= 0)
                    {
                        MessageBox.Show("Capture instance must be selected");
                        return;
                    }
                    else
                    {
                        String sDdlDisableCDCTable = "EXEC sys.sp_cdc_disable_table ";

                        sDdlDisableCDCTable += " @source_schema = '" + drSelected[0]["source_schema"].ToString() + "',";
                        sDdlDisableCDCTable += " @source_name = '" + drSelected[0]["source_table"].ToString() + "',";
                        if (oDisableCDCTable.m_sSelectedCaptureInstance == " All")
                            sDdlDisableCDCTable += " @capture_instance = 'all'";
                        else
                            sDdlDisableCDCTable += " @capture_instance = '" + oDisableCDCTable.m_sSelectedCaptureInstance + "'";

                        m_sErrorMessage = String.Empty;
                        if (false == m_oCDCManagement.CDCDisableCDCTable(m_sConnectionString, ref sDdlDisableCDCTable, ref m_sErrorMessage))
                        {
                            Cursor.Current = Cursors.Default;

                            MessageBox.Show("There was an error while disabling CDC. Make sure the user has DB_OWNER or SYSADMIN rights.", m_sFormTitle, MessageBoxButtons.OK, MessageBoxIcon.Error);
                        }

                        LoadCDCManagementInfo();
                    }
                }
                
            }
            catch (Exception ex)
            {
                Cursor.Current = Cursors.Default;

                MessageBox.Show("There was an error while disabling CDC. Make sure the user has DB_OWNER or SYSADMIN rights.", m_sFormTitle, MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }

        private void btnGenDisableScripts_Click(object sender, EventArgs e)
        {
            try
            {
                
                String sDdlDisableCDCTable = String.Empty;
                sDdlDisableCDCTable += "/* ";
                sDdlDisableCDCTable += Environment.NewLine;
                sDdlDisableCDCTable += "Scripts to disable CDC on tables.";
                sDdlDisableCDCTable += Environment.NewLine;
                sDdlDisableCDCTable += "Created on: " + DateTime.Now.ToString("dd/MMM/yyyy HH:mm:ss ");
                sDdlDisableCDCTable += Environment.NewLine;
                sDdlDisableCDCTable += "*/ ";

                sDdlDisableCDCTable += Environment.NewLine;
                sDdlDisableCDCTable += Environment.NewLine;

                sDdlDisableCDCTable += "EXEC sys.sp_cdc_disable_db " ;
                sDdlDisableCDCTable += Environment.NewLine;
                sDdlDisableCDCTable += "GO";
                sDdlDisableCDCTable += Environment.NewLine;
                
                foreach (DataRow drCDCTables in m_dtCDCEnabledTables.Rows)
                {
                    sDdlDisableCDCTable += "EXEC sys.sp_cdc_disable_table ";
                    sDdlDisableCDCTable += Environment.NewLine;
                    sDdlDisableCDCTable += " @source_schema = '" + drCDCTables["source_schema"].ToString() + "',";
                    sDdlDisableCDCTable += Environment.NewLine;
                    sDdlDisableCDCTable += " @source_name = '" + drCDCTables["source_table"].ToString() + "',";
                    sDdlDisableCDCTable += Environment.NewLine;
                    sDdlDisableCDCTable += " @capture_instance = '" + drCDCTables["capture_instance"].ToString() + "'";

                    sDdlDisableCDCTable += Environment.NewLine;
                    sDdlDisableCDCTable += "GO";
                    sDdlDisableCDCTable += Environment.NewLine;
                }


                sfdSaveScripts.FileName = "Scripts_DisbaleCDC.sql";
                sfdSaveScripts.Filter = "Script Files (*.sql)|*.sql|All Files (*.*)|*.*";
                
                if (DialogResult.OK == sfdSaveScripts.ShowDialog())
                {
                    System.IO.File.WriteAllText(sfdSaveScripts.FileName, sDdlDisableCDCTable);
                }

                MessageBox.Show("Scripts saved successfully.", m_sFormTitle, MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
            catch (Exception ex)
            {
                Cursor.Current = Cursors.Default;

                MessageBox.Show("There was an error while disabling CDC. Make sure the user has DB_OWNER or SYSADMIN rights. Error message: " + ex.Message, m_sFormTitle, MessageBoxButtons.OK, MessageBoxIcon.Error);
            }

        }

        private void btnGenEnableScripts_Click(object sender, EventArgs e)
        {
            try
            {

                String sDDLEnableCDC = String.Empty;
                m_sErrorMessage = String.Empty;
                sDDLEnableCDC = m_oCDCManagement.GetCDCEnableScript(m_sConnectionString, ref m_dtCDCEnabledTables, ref m_sErrorMessage);

                sfdSaveScripts.FileName = "Scripts_EnableCDC.sql";
                sfdSaveScripts.Filter = "Script Files (*.sql)|*.sql|All Files (*.*)|*.*";

                if (DialogResult.OK == sfdSaveScripts.ShowDialog())
                {
                    System.IO.File.WriteAllText(sfdSaveScripts.FileName, sDDLEnableCDC);
                }

                MessageBox.Show("Scripts saved successfully.", m_sFormTitle, MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
            catch (Exception ex)
            {
                Cursor.Current = Cursors.Default;

                MessageBox.Show("There was an error while generating enable CDC scripts. Error message: " + ex.Message, m_sFormTitle, MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }

        
        private void txtNewTableName_TabIndexChanged(object sender, EventArgs e)
        {
            try
            {
                if (txtNewTableName.Text.Length > 0 &&
                    txtNewSchemaName.Text.Length > 0)
                {
                    txtNewCaptureInstance.Text = txtNewSchemaName.Text + "." + txtNewTableName.Text;
                    chkIncludeAllColumns.Visible = true;
                    chkIncludeAllColumns.Checked = true;
                }
                else
                    chkIncludeAllColumns.Visible = false;
            }
            catch (Exception ex)
            {
                MessageBox.Show(m_sGenericErrorMessage + ex.Message, m_sFormTitle + " - Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }

        
        private void lstSelectedCaptureInstance_MouseClick(object sender, MouseEventArgs e)
        {
            try
            {
                System.Data.DataRowView drvSelected = (System.Data.DataRowView)lstSelectedCaptureInstance.SelectedItem;
                if (null == drvSelected && null == drvSelected.Row)
                {
                    Cursor.Current = Cursors.Default;
                    return;
                }
                String sSelectedCapInstance = drvSelected.Row[2].ToString();

                String sFilter = "table_fullname='" + lblSelectedTable.Text + "' ";
                sFilter += " AND capture_instance='" + sSelectedCapInstance + "'";
                DataRow[] drSelected = m_dtCDCEnabledTables.Select(sFilter);
                txtSelectedColumns.Text = drSelected[0]["captured_column_list"].ToString();
                txtSelectedFileGroup.Text = drSelected[0]["filegroup_name"].ToString();

                txtSelectedIndexName.Text = drSelected[0]["index_name"].ToString();
                txtSelectedRoleName.Text = drSelected[0]["role_name"].ToString();
                txtSelectedIndexColumns.Text = drSelected[0]["index_column_list"].ToString();

                if (drSelected[0]["supports_net_changes"].ToString() == "0")
                {
                    txtSelectedNetChanges.Text = "All Changes";
                }
                else
                    txtSelectedNetChanges.Text = "All Changes (Including net changes)";


            }
            catch (Exception ex)
            {
                MessageBox.Show(m_sGenericErrorMessage + ex.Message, m_sFormTitle + " - Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }

        private void CDCManagement_Load(object sender, EventArgs e)
        {
            try
            {
                LoadCDCManagementInfo();
            }
            catch (Exception ex)
            {
                MessageBox.Show(m_sGenericErrorMessage + ex.Message, m_sFormTitle + " - Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }

        private void txtNewSchemaName_TabIndexChanged(object sender, EventArgs e)
        {
            try
            {
                if (txtNewTableName.Text.Length > 0 &&
                    txtNewSchemaName.Text.Length > 0)
                {
                    txtNewCaptureInstance.Text = txtNewSchemaName.Text + "." + txtNewTableName.Text;
                    chkIncludeAllColumns.Visible = true;
                    chkIncludeAllColumns.Checked = true;
                }
                else
                    chkIncludeAllColumns.Visible = false;
            }
            catch(Exception ex)
            {
                MessageBox.Show(m_sGenericErrorMessage + ex.Message, m_sFormTitle + " - Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }

        private void txtNewSchemaName_TextChanged(object sender, EventArgs e)
        {
            try
            {
                if (txtNewTableName.Text.Length > 0 &&
                    txtNewSchemaName.Text.Length > 0)
                {
                    txtNewCaptureInstance.Text = txtNewSchemaName.Text + "." + txtNewTableName.Text;
                    chkIncludeAllColumns.Visible = true;
                    chkIncludeAllColumns.Checked = true;
                }
                else
                    chkIncludeAllColumns.Visible = false;
            }
            catch (Exception ex)
            {
                MessageBox.Show(m_sGenericErrorMessage + ex.Message, m_sFormTitle + " - Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }
    }
}
